# 导入flask类
# render_template 从 jinjia2的库自动带的

from flask import Flask, request, render_template
from datetime import datetime
# pymysql 连接数据库的驱动, SQLAlchemy 类似jdbc可以简单的操作数据库
from flask_sqlalchemy import SQLAlchemy
import sqlalchemy
import json
from sqlalchemy.sql import text

app = Flask(__name__)

HOSTNAME = "127.0.0.1"
PORT = 3306
USERNAME = "root"
PASSWORD = "root"
DATABASE = "authbase"
app.config["SQLALCHEMY_DATABASE_URI"] = f"mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8mb4"
db = SQLAlchemy()
# SQLAlchemy 会自动的读取confg中的配置信息
db.init_app(app)


class User(db.Model):
    __tablename__ = 'testuser'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(100), nullable=False)
    password = db.Column(db.String(100), nullable=False)



# 创建路由,返回页面渲染函数
@app.route("/")
def index():
    return "sdfsd"


# 创建路由,返回页面渲染函数
@app.route("/user/list")
def user_list():
    res = db.session.execute(text(
        'select  cs.id, count( w.id) as count ,cs.name FROM tb_construction_site cs LEFT JOIN tb_worker w on  cs.id = w.construction_id  GROUP BY cs.id ,cs.NAME')).all()
    return json.dumps({'code': 0, 'msg': '查询成功', 'data': [row._asdict() for row in res]})



if __name__ == '__main__':
    # debug=True, 开启bug模模式,修改代码数据重新加载,热部署
    # 修改端口,8888
    # 修改host,监听主机名,0.0.0.0 通过ip地址就可以让其他的电脑访问项目
    app.run(host='0.0.0.0', port=6000, debug=True)
